//
//  DataBaseHandle.m
//  UI21_数据库练习
//
//  Created by dllo on 15/10/15.
//  Copyright (c) 2015年 lanou3g. All rights reserved.
//
#if 0
#import "DataBaseHandle.h"
#import "CRUserModel.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle {
    static DataBaseHandle *handle;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[DataBaseHandle alloc] init];
    });
    [handle openDB];
    return handle;
}

- (void)openDB {
    // 1.沙盒路径
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES) lastObject];
    NSString *dbPath = [sandBoxPath stringByAppendingString:@"User.sqlite"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    }
    else {
        NSLog(@"打开失败");
    }
    NSLog(@"%@", dbPath);
}

- (void)creatTable {
//    NSString *sqlStr = @"create table if not exists movie (movieId text primary key, movieName text, picurl text)";
    NSString *sqlStr = @"create table if not exists user (userId text, userName text, passWord text, iconUrl text, nickName text, sex text, age text, address text, sign text, tel text)";
    // 执行语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建成功");
    }
    else {
        NSLog(@"创建失败");
    }
}

- (SelectUser)isHaveInTable:(NSString *)str type:(loginType)type {
    if (type == Weibo) {
        // 除了openDB,都先写语句
        NSString *sqlStr = [NSString stringWithFormat:@"select * from user where userId = '%@'", str];
        // 跟随指针
        sqlite3_stmt *stmt = nil;
        int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
        if (result == SQLITE_OK) {
            NSLog(@"查询成功");
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                sqlite3_finalize(stmt);
                return InTable;
            }
            else {
                sqlite3_finalize(stmt);
                return NotInTable;
            }
        }
        else {
            NSLog(@"查询失败");
            sqlite3_finalize(stmt);
            return SelectError;
        }
    }
    else if (type == Normal) {
        // 除了openDB,都先写语句
        NSString *sqlStr = [NSString stringWithFormat:@"select * from user where userName = '%@'", str];
        // 跟随指针
        sqlite3_stmt *stmt = nil;
        int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
        if (result == SQLITE_OK) {
            NSLog(@"查询成功");
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                sqlite3_finalize(stmt);
                return InTable;
            }
            else {
                sqlite3_finalize(stmt);
                return NotInTable;
            }
        }
        else {
            NSLog(@"查询失败");
            sqlite3_finalize(stmt);
            return SelectError;
        }
    }
    return SelectError;
}

- (void)saveUserInDB:(ZBUserModel *)user {
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into user (userId, userName, password, iconUrl, nickName, sex, age, address, sign, tel) values ('%@', ' ', ' ', '%@', '%@', ' ', ' ', ' ', ' ', ' ')", user.userId, user.iconUrl, user.nickName];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    }
    else {
        NSLog(@"添加失败");
    }
    
}

- (void)upDateUserInDB:(ZBUserModel *)user type:(loginType)type {
    if (type == Normal) {
//        NSString *sqlStr = [NSString stringWithFormat:@"update user set %@ = %@ where userName = '%@'", key, value, str];
//        int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
//        if (result == SQLITE_OK) {
//            NSLog(@"删除成功");
//        }
//        else {
//            NSLog(@"删除失败");
//        }
    }
    else if (type == Weibo) {
        NSString *sqlStr = [NSString stringWithFormat:@"update user set nickName = '%@', sex = '%@', age = '%@', address = '%@', sign = '%@' where userId = '%@'", user.nickName, user.sex, user.age, user.address, user.sign, user.userId];
        int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
        if (result == SQLITE_OK) {
            NSLog(@"删除成功");
        }
        else {
            NSLog(@"***%d", result);
        }
    }
}

- (ZBUserModel *)selectUser:(loginType)type str:(NSString *)str {
    ZBUserModel *user = [[ZBUserModel alloc] init];
    if (type == Weibo) {
        NSString *sqlStr = [NSString stringWithFormat:@"select * from user where userId = '%@'", str];
        //    NSString *sqlStr = @"select * from movie";
        // 跟随指针
        sqlite3_stmt *stmt = nil;
        // 执行查找
        int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
        if (result == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                user.userId = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
                user.userName = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
                user.passWord = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
                user.iconUrl = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
                user.nickName = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)];
                user.sex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 5)];
                user.age = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 6)];
                user.address = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 7)];
                user.sign = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 8)];
                user.tel = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 9)];
                sqlite3_finalize(stmt);
                return user;
            }
        }
        sqlite3_finalize(stmt);
    }
    [user release];
    return nil;
}

- (void)setTel:(NSString *)str tel:(NSString *)tel {
    NSString *sqlStr = [NSString stringWithFormat:@"update user set tel = '%@' where userId = '%@'", tel, str];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else {
        NSLog(@"***%d", result);
    }
}

@end
#endif